﻿# -*- coding: utf-8 -*-
"""
Created on Wed May 26 16:47:17 2021

@author: huangyue
"""

'''
提取数据所用
'''
import pandas as pd
import datetime
import pymssql

d1 = datetime.timedelta(days=1)


# 连接聚源的参数
server_jydb = "10.10.0.102"
user_jydb = "jydb"
password_jydb = "jydb"

# %% 获得相应code的指数行情序列
def get_indexquote(cursor_jydb, code,begdate=None,enddate=None):
    # conn_jydb = pymssql.connect(server_jydb, user_jydb, password_jydb, 'JYDB', charset='cp936')
    # cursor_jydb = conn_jydb.cursor()
    
    if begdate==None:
        begdate=datetime.datetime(2017,1,1)
    if enddate==None:
        enddate = datetime.datetime.now() - d1*16/24
        enddate = enddate+d1*(4-(enddate.weekday() if enddate.weekday()>=5 else 4))
    if not isinstance(begdate,str):
        begdate=begdate.strftime('%Y-%m-%d')
    if not isinstance(enddate,str):
        enddate=enddate.strftime('%Y-%m-%d')
    
    # 市场
    market = code.split('.')[1]
    SecuMarket=83 if (market=='SH') else (90 if market=='SZ' else 84)               
    # 代码
    SecuCode = code.split('.')[0]

    str_sql=\
    '''
    select TradingDay,ClosePrice
    FROM QT_IndexQuote,
    (SELECT InnerCode FROM SecuMain WHERE SecuCode='%s' and SecuMarket=%s) as secucode
    where QT_IndexQuote.InnerCode=secucode.InnerCode
    and TradingDay>='%s' 
    and TradingDay<='%s'
    '''%(SecuCode,SecuMarket,begdate,enddate)
        
    # 数据提取
    cursor_jydb.execute(str_sql)
    quotedata = pd.DataFrame(cursor_jydb.fetchall(), 
                             columns=['date',code])
    quotedata = quotedata.astype({'date':'datetime64[ns]',code:'float32'})
    
    # cursor_jydb.close()
    # conn_jydb.close()
    
    return quotedata.sort_values(by='date').reset_index(drop=True)

# %%
def get_basicinfo(cursor):
    #基本信息    
    str_basicinfo = '''
    SELECT 
    Bond_ConBDBasicInfo.InnerCode,
    Bond_Code.SecuCode as CBCode,
    Bond_Code.SecuAbbr as CBAbbr,
    Bond_Code.SecuMarket as CBSecuMarket,
    Bond_ConBDBasicInfo.SecuCode as SCode,
    Bond_ConBDBasicInfo.SecuAbbr as SAbbr,
    Bond_Code.ListedDate,
    ActualIssueSize
    FROM Bond_Code,Bond_ConBDBasicInfo    /*正股的交易场所需要从代码中推算*/
    where 
    Bond_Code.InnerCode=Bond_ConBDBasicInfo.InnerCode
    and  Bond_Code.SecuMarket in (90,83) --仅保留上交所深交所
    and BondForm=1  --仅保留可转债
    order by InnerCode'''

    # 数据提取
    cursor.execute(str_basicinfo)
    basicinfo = pd.DataFrame(cursor.fetchall(),
                             columns=['InnerCode','CBCode','CBAbbr','Exchange','SCode','SAbbr','ListedDate','IssueSize'])

    return basicinfo
    
def get_quote(cursor,begdate,enddate=None):
    #行情数据    
    if (begdate==enddate) | (enddate==None):
        str_quote='''
            SELECT InnerCode,TradingDay,ConvertPremiumRate,CBConvertValue,TurnoverRate,ClosePrice
            FROM Bond_ConBDExchangeQuote 
            WHERE TradingDay='%s'
            ORDER BY InnerCode,TradingDay''' % (begdate)
    else:
        str_quote='''
            SELECT InnerCode,TradingDay,ConvertPremiumRate,CBConvertValue,TurnoverRate,ClosePrice
            FROM Bond_ConBDExchangeQuote 
            WHERE TradingDay>='%s' and TradingDay <= '%s'
            ORDER BY InnerCode,TradingDay''' % (begdate,enddate)

    # 数据提取
    cursor.execute(str_quote)
    quote = pd.DataFrame(cursor.fetchall(),
                         columns=['InnerCode','TradeDay','PremiumRate','ParPrice','TurnoverRate','ClosePrice'])    

    return quote



def get_amountdata(cursor):
    #余额数据    
    str_amt='''
    SELECT InnerCode,EndDate,EventType,IssueAmount,RemainingAmount
    FROM Bond_ConBDConvertInfo'''
    
    # 数据提取
    cursor.execute(str_amt)
    amtdata = pd.DataFrame(cursor.fetchall(),
                             columns=['InnerCode','EndDay','EvenType','IssueAmt','RemainAmt'])
    return amtdata




